USE [TAW]
GO

/****** Object:  Table [dbo].[OEE_Record]    Script Date: 06/20/2012 14:56:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[OEE_Record](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Date] [date] NOT NULL,
	[Shift] [varchar](5) NOT NULL,
	[CellNo] [varchar](5) NOT NULL,
	[ActualDate] [datetime] NULL,
	[EquipName] [nvarchar](20) NULL,
	[ScheduledDownTime] [int] NULL,
 CONSTRAINT [PK_OEE_Record_1] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

USE [TAW]
GO

/****** Object:  Table [dbo].[OEE_Scrap]    Script Date: 06/20/2012 14:56:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[OEE_Scrap](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[OEEID] [int] NOT NULL,
	[RecordID] [int] NOT NULL,
	[ScrpCode] [int] NOT NULL,
	[ScrpQty] [int] NOT NULL,
 CONSTRAINT [PK_OEE_Scrap_1] PRIMARY KEY CLUSTERED 
(
	[ID] ASC,
	[OEEID] ASC,
	[RecordID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [TAW]
GO

/****** Object:  Table [dbo].[OEE_Staff]    Script Date: 06/20/2012 14:56:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[OEE_Staff](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[OEEID] [int] NOT NULL,
	[RecordID] [int] NOT NULL,
	[StaffNumber] [varchar](10) NOT NULL,
	[ProcessStep] [varchar](10) NULL,
	[FNumber] [varchar](30) NULL,
	[StdOutput] [int] NULL,
	[OperNum] [decimal](18, 1) NULL,
 CONSTRAINT [PK_OEE_Staff1] PRIMARY KEY CLUSTERED 
(
	[ID] ASC,
	[OEEID] ASC,
	[RecordID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

USE [TAW]
GO

/****** Object:  Table [dbo].[OEE_SubRecord]    Script Date: 06/20/2012 14:56:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[OEE_SubRecord](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[OEEID] [int] NOT NULL,
	[ProcessStep] [varchar](10) NOT NULL,
	[K3Code] [varchar](30) NOT NULL,
	[ActualOuput] [int] NULL,
	[StdOutput] [int] NULL,
	[OperatorNum] [float] NULL,
	[MouldNumber] [varchar](30) NULL,
 CONSTRAINT [PK_OEE_SubRecord] PRIMARY KEY CLUSTERED 
(
	[ID] ASC,
	[OEEID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[OEE_Scrap] ADD  CONSTRAINT [DF_OEE_Scrap_ScrpQty]  DEFAULT ((0)) FOR [ScrpQty]
GO


USE [TAW]
GO

/****** Object:  View [dbo].[OEE_View]    Script Date: 06/28/2012 13:18:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[OEE_View]
AS
SELECT     s.StaffNumber AS StaffNo, n.Name AS StaffName, n.Dept, m.Date AS WorkingDate, CONVERT(decimal(18, 2), SUM(r.ActualOuput * 8.00 * (CASE WHEN isnull(s.OperNum,
                       0) > 1 THEN 1 ELSE s.OperNum END) / r.StdOutput)) AS OEETime
FROM         dbo.OEE_Staff AS s LEFT OUTER JOIN
                      dbo.OEE_SubRecord AS r ON s.RecordID = r.ID AND s.OEEID = r.OEEID LEFT OUTER JOIN
                      dbo.OEE_Record AS m ON m.ID = s.OEEID LEFT OUTER JOIN
                      dbo.Namelist AS n ON n.StaffNo = s.StaffNumber
GROUP BY s.StaffNumber, m.Date, n.Name, n.Dept

GO

